import pprint
import time
from django.db import connections, DEFAULT_DB_ALIAS
from django.db.utils import ConnectionHandler, OperationalError
from django.db.models.signals import post_save, post_delete
from django.dispatch import receiver
from apps.databasetest.models import DatabaseConfig
from threading import Lock


class DynamicDBManager:
    # 记录配置的最后更新时间（用于判断是否需要刷新）
    _last_refresh_time = 0
    # 线程锁（避免并发刷新导致的问题）
    _lock = Lock()

    @classmethod
    def load_third_party_dbs(cls, force_refresh=False):
        """
        加载第三方数据库配置，支持强制刷新
        :param force_refresh: 是否强制刷新（忽略缓存时间）
        """
        with cls._lock:  # 加锁防止并发问题
            # 1. 检查是否需要刷新（默认30秒内不重复刷新，除非强制）
            current_time = time.time()
            if not force_refresh and (current_time - cls._last_refresh_time) < 30:
                return  # 30秒内已刷新过，直接返回

            # 2. 记录当前刷新时间
            cls._last_refresh_time = current_time

            # 3. 获取最新启用的配置
            third_party_dbs = DatabaseConfig.objects.filter(is_active=True)
            current_aliases = {db.db_alias for db in third_party_dbs}  # 当前应存在的别名
            existing_aliases = set(connections.databases.keys()) - {DEFAULT_DB_ALIAS}  # 现有非默认别名

            # 4. 处理需要移除的旧配置（已删除或禁用的数据库）
            for alias in existing_aliases - current_aliases:
                cls._remove_db_config(alias)

            # 5. 处理需要添加/更新的配置
            for db in third_party_dbs:
                cls._update_db_config(db)

    @classmethod
    def _update_db_config(cls, db):
        """更新单个数据库配置（新增或修改）"""
        # 构建最新配置
        db_config = cls._build_db_config(db)

        # 检查是否需要更新（配置有变化才刷新）
        if db.db_alias in connections.databases:
            existing_config = connections.databases[db.db_alias]
            # 排除动态变化的字段（如时间），比较核心配置
            if cls._config_equal(existing_config, db_config):
                print(f"数据库配置未变化：{db.db_alias}")
                return

        # 更新配置并重建连接
        connections.databases[db.db_alias] = db_config
        cls._reconnect_db(db.db_alias)  # 关闭旧连接，确保使用新配置
        print(f"已更新 {db.get_db_type_display()} 配置：{db.db_alias}")
        pprint.pprint(db_config)

    @classmethod
    def _remove_db_config(cls, alias):
        """移除数据库配置并关闭连接"""
        if alias in connections.databases:
            del connections.databases[alias]  # 从配置中移除
            cls._reconnect_db(alias)  # 关闭旧连接
            print(f"已移除数据库配置：{alias}")

    @classmethod
    def _build_db_config(cls, db):
        """构建数据库配置（按类型适配）"""
        db_config = {
            "ENGINE": db.engine,
            "NAME": db.db_name,
            "USER": db.user,
            "PASSWORD": db.password,
            "HOST": db.host,
            "PORT": db.port or db.get_default_port(),
            "ATOMIC_REQUESTS": db.atomic_requests,
            "CONN_HEALTH_CHECKS": db.conn_health_checks,
            "CONN_MAX_AGE": db.conn_max_age,
            "AUTOCOMMIT": db.autocommit,
            "TIME_ZONE": db.time_zone
        }

        # 按数据库类型添加默认参数
        db_type = db.db_type
        default_options = {}
        if db_type == "mysql":
            default_options = db.options
        elif db_type == "mssql":
            default_options = db.options
        elif db_type == "oracle":
            default_options = {"service_name": db.db_name, "encoding": "UTF-8"}
        elif db_type == "postgresql":
            default_options = {"options": "-c client_encoding=utf8"}
        elif db_type == "sqlite3":
            db_config.pop("HOST")
            db_config.pop("PORT")
            default_options = {"timeout": 30}

        # 合并用户配置（用户配置优先）
        user_options = db.options or {}
        db_config["OPTIONS"] = {**default_options, **user_options}

        return db_config

    @classmethod
    def _config_equal(cls, config1, config2):
        """比较两个配置是否相同（忽略无关字段）"""
        # 排除动态变化的字段，只比较核心配置
        ignore_fields = ["TIME_ZONE"]  # 不需要比较的字段
        filtered1 = {k: v for k, v in config1.items() if k not in ignore_fields}
        filtered2 = {k: v for k, v in config2.items() if k not in ignore_fields}
        return filtered1 == filtered2

    @classmethod
    def _reconnect_db(cls, alias):
        """关闭旧连接，强制使用新配置重建连接"""
        try:
            # 关闭指定别名的所有连接
            if alias in connections:
                connections[alias].close()
                print(f"已关闭旧连接：{alias}")
        except OperationalError as e:
            print(f"关闭连接 {alias} 失败：{str(e)}")

    @classmethod
    def get_db_alias(cls, request):
        return request.query_params.get("db_alias")

    @classmethod
    def use_db(cls, db_alias):
        """使用数据库前先确保配置是最新的"""
        # 每次使用前检查是否需要刷新（轻量化检查）
        cls.load_third_party_dbs()

        from contextlib import contextmanager
        @contextmanager
        def _use_db_context():
            original_default = connections._connections.default
            try:
                connections._connections.default = connections[db_alias]
                yield
            finally:
                connections._connections.default = original_default

        return _use_db_context()


# 监听数据库配置表的变化，自动刷新连接池
@receiver(post_save, sender=DatabaseConfig)
@receiver(post_delete, sender=DatabaseConfig)
def refresh_db_config_on_change(sender, instance, **kwargs):
    """当配置表新增/修改/删除时，强制刷新连接池"""
    print(f"检测到 {sender.__name__} 变化，正在刷新连接池...")
    DynamicDBManager.load_third_party_dbs(force_refresh=True)
